UseCases/Scaling/Demo 2 - Real-Time Monitoring.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "reduced-matrix",
   "metadata": {},
   "source": [
    "<header style=\"padding:10px;background:#f9f9f9;border-top:3px solid #00b2b1\"><img id=\"Teradata-logo\" src=\"https://www.teradata.com/Teradata/Images/Rebrand/Teradata_logo-two_color.png\" alt=\"Teradata\" width=\"220\" align=\"right\" />\n",
    "\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#E37C4D'><b>VantageCloud Lake Systems Scaling and Monitoring</b></p>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "orange-firewall",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#E37C4D'>Demo 2 - Real-Time monitoring of Key Metrics</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>This notebook is a short set of python code designed to illustrate how key metrics such as compute cluster status, active user sessions, and CPU utilization can be monitored using dashboards or other monitoring tools</p>\n",
    "\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#E37C4D'>Demonstration Overview</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>This notebook consists of three primary demonstrations</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li><b style = 'color:#00b2b1'>Connect to the VantageCloud Lake System</b> - Connect as a user with access to the metrics service and performance monitoring functions</li>\n",
    "    <li><b style = 'color:#00b2b1'>Key Metrics Queries</b> - Queries that monitor active users, Cluster CPU stats, and number of instances</li>\n",
    "    <li><b style = 'color:#00b2b1'>Dasbhboard</b> - Update and plot stats every three seconds</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sought-hometown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Python Package Imports</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>\n",
    "Import the required packages including teradata client packages (teradataml and teradatasql) as well as packages for plotting and data management.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "unable-announcement",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import teradatasql, logging, time, math, json\n",
    "from teradataml import *\n",
    "import getpass\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from time import sleep\n",
    "from random import random\n",
    "from IPython.display import display\n",
    "\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.animation as animation\n",
    "from IPython.display import clear_output\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fitting-kinase",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Connect to Vantage</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Before performing any operations in Vantage, we need to connect to the system.  The below code will read in a variables file (vars.json - this has been used in prior environment setup and data engineering examples) and will connect to Vantage with this information.  The Vantage connection is referred to as a \"Context\" - a common python-rdbms connection architecture.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "figured-hybrid",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a local dictionary of environment-specific variables\n",
    "\n",
    "# load vars json\n",
    "with open('../../vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "# Use the \"data_engineer\" and Business compute group from the base setup\n",
    "host = session_vars['environment']['host']\n",
    "username = session_vars['hierarchy']['users']['business_users'][0]['username']\n",
    "password = session_vars['hierarchy']['users']['business_users'][0]['password']\n",
    "compute_group = session_vars['hierarchy']['users']['business_users'][0]['compute_group']\n",
    "\n",
    "conn_info = {}\n",
    "conn_info['host'] = host\n",
    "conn_info['username'] = username\n",
    "conn_info['password'] = password\n",
    "conn_info['compute_group'] = compute_group\n",
    "\n",
    "eng = create_context(host = conn_info['host'], username = conn_info['username'], password = conn_info['password'])\n",
    "\n",
    "# confirm connection\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "valued-house",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#E37C4D'><b>Key Metrics Queries</b></p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>\n",
    "Execute initial queries to collect Compute Profile status, Active user sessions, and Physical CPU percentage utilization.  More information on these and other queries are found in Demonstration 3.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "brave-marriage",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Query to get compute profile status - Active, Hibernated, etc.\n",
    "clusters_query = '''\n",
    "SELECT * FROM dbc.computestatusv\n",
    "ORDER BY ComputeProfileName, CurrentState;\n",
    "'''\n",
    "\n",
    "# AmpState \"Active\" is a good way to see in-flight queries \n",
    "cons_query = '''\n",
    "SELECT HostId (TITLE 'Host',FORMAT 'ZZZ9'),\n",
    "    UserName (TITLE 'Active Users'),\n",
    "    SessionNo (TITLE 'Session'),\n",
    " LogonPENo (TITLE 'IFP')\n",
    "FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)) AS t1\n",
    "WHERE AmpState='Active'\n",
    "ORDER BY 1,2;\n",
    "'''\n",
    "\n",
    "# A simple query to collect CPU Utilization on physical resources\n",
    "all_cpus_query = '''\n",
    "SELECT \n",
    "CASE\n",
    "WHEN Name ='1' then 'Primary Cluster'\n",
    "ELSE Name\n",
    "END as Name, AVG(CPUUse) as CPUUse\n",
    "FROM syslib.MonitorPhysicalResourceSV(USING details('1')) AS dt\n",
    "GROUP BY 1;\n",
    "'''\n",
    "\n",
    "\n",
    "\n",
    "try:\n",
    "    df_concurrency = pd.read_sql(cons_query, eng)\n",
    "except Exception as e:\n",
    "        if str(e.args).find(\"Error 7559\") >= 1: # check for occasional malloc issue\n",
    "            df_concurrency = pd.read_sql(cons_query, eng)\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "\n",
    "df_all_cpus = pd.DataFrame()           \n",
    "df_cpu = pd.read_sql(all_cpus_query, eng)\n",
    "df_cpu['id'] = 0\n",
    "df_all_cpus = pd.concat([df_all_cpus, df_cpu.pivot(index = 'id', columns = 'Name', values = 'CPUUse')])\n",
    "\n",
    "df_clusters = pd.read_sql(clusters_query, eng)\n",
    "df_cluster_count = df_clusters[df_clusters['CurrentState'] == 'Active'].groupby('ComputeProfileName').count()[['InstanceName']]\n",
    "df_cluster_count['id'] = 0\n",
    "df_all_clusters = df_cluster_count.reset_index().pivot(index = 'id', columns = 'ComputeProfileName', values = 'InstanceName')\n",
    "\n",
    "plt.rcParams[\"figure.figsize\"] = (20,5)\n",
    "\n",
    "x = np.array([[0, df_concurrency.shape[0]]])\n",
    "p = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "amateur-cross",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#E37C4D'><b>Dashboard</b></p>\n",
    "\n",
    "\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>\n",
    "A simple Python loop using matplotlib plotting tools can be used to simulate a dashboard or other monitoring tool.  To exit this loop, wait the desired iterations, or choose Kernel->Interrupt from the Jupyter menu.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "nasty-designer",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    for i in range(1200):\n",
    "\n",
    "        p+=1 # keep a persistent value to maintain plot index\n",
    "        fig, (ax1, ax2, ax3) = plt.subplots(ncols = 3)\n",
    "\n",
    "        try:\n",
    "            df_concurrency = pd.read_sql(cons_query, eng)\n",
    "        except Exception as e:\n",
    "            if str(e.args).find(\"Error 7559\") >= 1: # check for occasional malloc issue\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "        except KeyboardInterrupt:\n",
    "            pass\n",
    "\n",
    "        df_cpu = pd.read_sql(all_cpus_query, eng)\n",
    "        df_cpu['id'] = p\n",
    "        df_all_cpus = pd.concat([df_all_cpus, df_cpu.pivot(index = 'id', columns = 'Name', values = 'CPUUse')])\n",
    "\n",
    "        df_clusters = pd.read_sql(clusters_query, eng)\n",
    "        df_cluster_count = df_clusters[df_clusters['CurrentState'] == 'ACTIVE'].groupby('ComputeProfileName').count()[['InstanceName']]\n",
    "        df_cluster_count['id'] = p\n",
    "        df_all_clusters = pd.concat([df_all_clusters, df_cluster_count.reset_index().pivot(index = 'id', columns = 'ComputeProfileName', values = 'InstanceName')])\n",
    "\n",
    "        x = np.append(x, [[p,df_concurrency.shape[0]]], axis = 0)\n",
    "\n",
    "        clear_output(wait=True)\n",
    "        sleep(3)\n",
    "\n",
    "        ax1.plot(x[:, 0], x[:, 1])\n",
    "        ax1.set_title('Concurrency')\n",
    "\n",
    "        df_all_cpus.sort_index().plot(kind = 'line', ax = ax2)\n",
    "        ax2.set_title('CPU Utilization')\n",
    "\n",
    "        df_all_clusters.fillna(0).sort_index().plot(kind = 'line', ax = ax3)\n",
    "        ax3.set_title('Instance Count')\n",
    "\n",
    "        plt.show()\n",
    "\n",
    "        display(df_clusters)\n",
    "        display(df_concurrency)\n",
    "\n",
    "except KeyboardInterrupt:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "measured-editing",
   "metadata": {},
   "source": [
    "Copyright 2023, Teradata Corporation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "juvenile-killer",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}